create or replace package DB_UTILS_P is
/*
|| created by : Csung, 2005, January
||
|| modification history:
*/
TYPE ODPNET_REFCURSOR IS REF CURSOR;
/*
|| de lay objectid
*/
function get_object_id return number;

/*
|| de commit tu phia clients
*/
procedure db_commit;

/*
|| de rollback tu phia clients
*/
procedure db_rollback;

/*
|| set save point
*/
procedure db_set_savepoint(ip_savepoint_name varchar2);
/*
|| rollback to save point
*/
procedure db_rollback_to_savepoint(ip_savepoint_name varchar2);

/**/
procedure trace_code(ip_str varchar2);
/* GENERATE column NAME  CODE*/
procedure gen_column_name(ip_str_table_name varchar2);
/* GENERATE all table NAME  CODE*/
procedure gen_table_name;
/*
|| get process lock and release on commit
*/
procedure get_lock( ip_lock_name varchar2);
/*
|| make us.property =
*/
procedure gen_make_us_object(ip_table_name varchar2);
/*
||
*/
procedure fill_data_type_column_in_doc;
/*
|| LAy id cua tmp object
*/
function get_tmp_object_id return number;
 /* END OF PACKAGE*/
 end DB_UTILS_P;
/
create or replace package body DB_UTILS_P is
/*
|| created by : Csung, 2005, January
||
|| modification history:
*/

/*
|| de lay objectid
*/
function get_object_id return number is
    v_next_id number;
begin
     select topica_object_id.nextval
     into v_next_id
     from dual;
     return v_next_id;
end get_object_id;

/*
|| de commit tu phia clients
*/
procedure db_commit is
begin
     commit;
end db_commit;
/*
|| de rollback tu phia clients
*/
procedure db_rollback is
begin
  rollback;
end db_rollback;

/*
|| set save point
*/
procedure db_set_savepoint(ip_savepoint_name varchar2) is
begin
 dbms_transaction.savepoint(ip_savepoint_name);
end db_set_savepoint;
/*
|| rollback to save point
*/
procedure db_rollback_to_savepoint(ip_savepoint_name varchar2) is
begin
 dbms_transaction.rollback_savepoint(ip_savepoint_name);
end db_rollback_to_savepoint;

procedure trace_code(ip_str varchar2) is
   PRAGMA AUTONOMOUS_TRANSACTION;
   v_id number := db_utils_p.get_object_id;
begin
     insert  into  sys_trace_table (id, trace_str)values (v_id,ip_str);
     commit;
end trace_code;
/* GENERATE COLUMN NAME  CODE*/
procedure gen_column_name(ip_str_table_name varchar2) is
   cursor v_cur_columns is select column_name
                           from   user_tab_columns
                           where  table_name = UPPER(ip_str_table_name);
   v_column_name user_tab_columns.COLUMN_NAME%type;
begin
   dbms_output.put_line('public class ' || upper(ip_str_table_name)|| '{');
   open v_cur_columns;
   loop
       fetch v_cur_columns into v_column_name;
       exit when v_cur_columns%notfound;
       dbms_output.put_line( '          public const string ' || upper(v_column_name)  || ' = ' ||'"'|| upper(v_column_name)||'";');
   end loop;
   dbms_output.put_line('}');
end gen_column_name;
/* GENERATE ALL TABLE NAME  CODE*/
procedure gen_table_name is
  cursor v_cur_tables is
                      select u.table_name
                      from   user_tables u
                      where u.table_name like 'CM_%'
                      or    u.table_name like 'HT_%';
  v_table_name user_tables.table_name%type;
begin
     dbms_output.put_line('public class TABLENAME {');
     open v_cur_tables;
     loop
         fetch v_cur_tables into v_table_name;
         exit when v_cur_tables%notfound;
         dbms_output.put_line('          public const string '|| upper(v_table_name) || ' = ' || '"' || upper(v_table_name) || '";');
     end loop;
         dbms_output.put_line('}');
end gen_table_name;
/*
|| get process lock and release on commit
*/
procedure get_lock( ip_lock_name varchar2) is
   v_handle VARCHAR2(128);
   v_status INTEGER;
BEGIN
   dbms_lock.allocate_unique( ip_lock_name, v_handle );
   v_status := dbms_lock.request( lockhandle => v_handle,
                                  lockmode => dbms_lock.x_mode,
                                  timeout => 1,
                                  release_on_commit =>  true );
   if v_status <> 0  then
      raise_application_error(-20001,'topica-resource busy');
   end if;
end get_lock;

/*
||fill data type column in doc
*/
procedure fill_data_type_column_in_doc is
 v_str varchar2(20);
begin
    for v_rec in ( select u.TABLE_NAME, u.COLUMN_NAME, u.DATA_TYPE, u.DATA_LENGTH, u.DATA_PRECISION, u.DATA_SCALE
                    from user_tab_columns u) loop
           if v_rec.DATA_TYPE = 'VARCHAR2' then
             v_str := 'VARCHAR2(' || v_rec.data_length ||')';
           elsif v_rec.DATA_TYPE = 'DATE' then
             v_str := 'DATE';
           elsif v_rec.DATA_TYPE = 'NUMBER' then
             v_str := 'NUMBER(' || v_rec.DATA_PRECISION ||','|| v_rec.DATA_SCALE ||')';
           end if;
           update SYS_DOC_COLUMN_COMMENT D
           set    D.DATA_TYPE = V_STR
           WHERE  D.TABLE_NAME = V_REC.TABLE_NAME AND D.COLUMN_NAME = V_REC.COLUMN_NAME;
    end loop;
end fill_data_type_column_in_doc;

/*
|| make us.property =
*/
procedure gen_make_us_object(ip_table_name varchar2) is

   cursor v_cur_columns is select u.column_name, u.DATA_TYPE
                           from   user_tab_columns u
                           where  table_name = UPPER(ip_table_name);
   v_column_name user_tab_columns.COLUMN_NAME%type;
   v_column_type user_tab_columns.DATA_TYPE%type;
   v_str varchar2(10);
begin

   open v_cur_columns;
   loop
       fetch v_cur_columns into v_column_name, v_column_type;
       exit when v_cur_columns%notfound;
       if v_column_type = 'VARCHAR2' or v_column_type = 'VARCHAR' then
              v_str := 'str';
       elsif  v_column_type = 'DATE' then
              v_str := 'dat';
       elsif v_column_type = 'NUMBER' then
              v_str := 'dc';
       end if;
       dbms_output.put_line( '          @_us_'||ip_table_name||'.' ||v_str|| upper(v_column_name)  || ' = ' );

   end loop;


end gen_make_us_object;

/*
|| LAy id cua tmp object
*/
function get_tmp_object_id return number is

     v_next_id number;
begin
     select tmp_object_seq.nextval
     into v_next_id
     from dual;
     return v_next_id;
end;


begin
  -- Initialization
  null;
end DB_UTILS_P;
/
